package com.hex.ds.hdtp.core.app.data.table.service.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.hex.ds.hdtp.core.app.common.page.PageData;
import com.hex.ds.hdtp.core.app.common.page.converter.IPageAndPageDtoConverter;
import com.hex.ds.hdtp.core.app.common.page.dto.PageDto;
import com.hex.ds.hdtp.core.app.common.util.ExcelDownUtil;
import com.hex.ds.hdtp.core.app.data.configuration.dto.requestDto.TableConvertRequestDto;
import com.hex.ds.hdtp.core.app.data.configuration.dto.response.TableConvertResponseDto;
import com.hex.ds.hdtp.core.app.data.configuration.service.ITableConvertService;
import com.hex.ds.hdtp.core.app.data.table.converter.ITableTransferFieldMapConverter;
import com.hex.ds.hdtp.core.app.data.table.converter.ITableTransferListConverter;
import com.hex.ds.hdtp.core.app.data.table.dto.request.TableTransferFieldMapRequestDto;
import com.hex.ds.hdtp.core.app.data.table.dto.request.TableTransferListRequestDto;
import com.hex.ds.hdtp.core.app.data.table.dto.request.TableTransferTaskRequestDto;
import com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferFieldMapResponseDto;
import com.hex.ds.hdtp.core.app.data.table.dto.excel.TableTransferListExcelDto;
import com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferListResponseDto;
import com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferTaskResponseDto;
import com.hex.ds.hdtp.core.app.data.table.service.ITableTransferFieldMapService;
import com.hex.ds.hdtp.core.app.data.table.service.ITableTransferListService;
import com.hex.ds.hdtp.core.app.data.table.service.ITableTransferTaskService;
import com.hex.ds.hdtp.core.app.metadata.service.ISourceInfoService;
import com.hex.ds.hdtp.core.inf.common.page.Page;
import com.hex.ds.hdtp.core.inf.data.configuration.po.TableConvertPo;
import com.hex.ds.hdtp.core.inf.data.contrast.service.impl.DataInfoSelectHandlerContext;
import com.hex.ds.hdtp.core.inf.data.table.po.TableTransferListPo;
import com.hex.ds.hdtp.core.inf.data.table.repository.ITableTransferListRepository;
import com.hex.ds.hdtp.core.inf.metadata.entity.TableFieldsEntity;
import com.hex.ds.hdtp.core.inf.metadata.po.SourceInfoPo;
import liquibase.pro.packaged.I;
import liquibase.util.file.FilenameUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.util.*;
import java.util.stream.Collectors;

import static com.hex.bigdata.hbda.utils.FileUtil.upload;

/**
 * @Package com.hex.ds.hdtp.core.app.data.table.service.impl
 * @ClassName TableTransferListService
 * @Description 表迁移清单-业务接口实现
 * @Author Wang zhihao
 * @Date 2023/8/9 11:37
 * @Version v1.0
 **/
@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class TableTransferListService implements ITableTransferListService {

    private final ITableTransferListRepository tableTransferListRepository;

    private final ITableTransferListConverter converter;

    private final ITableTransferFieldMapConverter tableTransferFieldMapConverter;

    private final IPageAndPageDtoConverter pageAndPageDtoConverter;

    private final ISourceInfoService sourceInfoService;

    private final DataInfoSelectHandlerContext dataInfoSelectHandlerContext;

    private final ITableTransferFieldMapService tableTransferFieldMapService;

    private final ITableConvertService tableConvertService;
    @Value("${file.path}")
    String filePath;

    @Autowired
    private ITableTransferTaskService tableTransferTaskService;

    /**
     * @Method queryListByPage
     * @Param
     * @param tableTransferListRequestDto
     * @param pageDto
     * @Return com.hex.ds.hdtp.core.app.common.page.PageData
     * @Description 表迁移清单-分页查询
     * @Author Wang zhihao
     * @Date 2023/8/9 11:35
     * @Version V1.0
     */
    @Override
    public PageData queryListByPage(TableTransferListRequestDto tableTransferListRequestDto, PageDto pageDto) {
        final Page<TableTransferListPo> pageResult = tableTransferListRepository.queryListByPage(
                converter.toTableTransferListPo(tableTransferListRequestDto), pageAndPageDtoConverter.toPage(pageDto));
        List<TableTransferListResponseDto> responseDtos = converter.toTableTransferListResponseDtoList(pageResult.getData());
        responseDtos.forEach(responseDto -> {
            if (!StringUtils.isEmpty(responseDto.getPartitionFieldName())) {
                responseDto.setPartitionFieldNameList(Arrays.asList(responseDto.getPartitionFieldName().split(",")));
            }
        });
        return new PageData(responseDtos, pageResult.getTotal());
    }

    /**
     * @Method queryPageListByTaskId
     * @Param tableTransferListRequestDto
     * @Param pageDto
     * @Return com.hex.ds.hdtp.core.app.common.page.PageData
     * @Description 根据任务主键分页查询
     * @Author gj.xu
     * @Date 2024/3/8 18:19
     * @Version V2.0
     */
    @Override
    public PageData queryPageListByTaskId(TableTransferListRequestDto tableTransferListRequestDto, PageDto pageDto) {
        final Page<TableTransferListPo> pageResult = tableTransferListRepository.queryPageListByTaskId(
                converter.toTableTransferListPo(tableTransferListRequestDto), pageAndPageDtoConverter.toPage(pageDto));
        List<TableTransferListResponseDto> responseDtos = converter.toTableTransferListResponseDtoList(pageResult.getData());
        responseDtos.forEach(responseDto -> {
            if (!StringUtils.isEmpty(responseDto.getPartitionFieldName())) {
                responseDto.setPartitionFieldNameList(Arrays.asList(responseDto.getPartitionFieldName().split(",")));
            }
        });
        return new PageData(responseDtos, pageResult.getTotal());
    }

    /**
     * @Method queryList
     * @Param
     * @param tableTransferListRequestDto
     * @Return java.util.List<com.hex.ds.hdtp.core.app.data.table.dto.request.TableTransferListRequestDto>
     * @Description 表迁移清单-不分页查询
     * @Author Wang zhihao
     * @Date 2023/8/10 14:28
     * @Version V1.0
     */
    @Override
    public List<TableTransferListResponseDto> queryList(TableTransferListRequestDto tableTransferListRequestDto) {
        List<TableTransferListPo> tableTransferList =
                tableTransferListRepository.queryList(converter.toTableTransferListPo(tableTransferListRequestDto));
        return converter.toTableTransferListResponseDtoList(tableTransferList);
    }

    /**
     * @Method queryLatestTransferTable
     * @Param
     * @Param sourceDbName
     * @Param sourceTableName
     * @Return com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferListResponseDto
     * @Description 根据源库名表名查询最新的迁移表信息
     * @Author gj.xu
     * @Date 2023/8/29 11:52
     * @Version V2.0
     */
    @Override
    public TableTransferListResponseDto queryLatestTransferTable(String sourceDbName, String sourceTableName) {
        TableTransferListPo tableTransfer = tableTransferListRepository.queryLatestTransferTable(sourceDbName, sourceTableName);
        return converter.toTableTransferListResponseDto(tableTransfer);
    }

    /**
     * @Method queryLatestTransferTableByTarget
     * @Param
     * @param targetDbName
     * @param targetTableName
     * @Return com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferListResponseDto
     * @Description 根据目标库名表名查询最新的迁移表信息
     * @Author Yin.Yang
     * @Date 2023/9/27 16:42
     * @Version V1.0
     */
    @Override
    public TableTransferListResponseDto queryLatestTransferTableByTarget(String targetDbName, String targetTableName, String targetPkId) {
        TableTransferListPo tableTransfer = tableTransferListRepository.queryLatestTransferTableByTarget(targetDbName, targetTableName, targetPkId);
        return converter.toTableTransferListResponseDto(tableTransfer);
    }

    /**
     * @Method queryById
     * @Param
     * @param pkId
     * @Return com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferListResponseDto
     * @Description 根据主键获取表迁移清单信息
     * @Author Wang zhihao
     * @Date 2023/8/14 14:41
     * @Version V1.0
     */
    @Override
    public TableTransferListResponseDto queryById(String pkId) {
        return converter.toTableTransferListResponseDto(tableTransferListRepository.queryById(pkId));
    }


    /**
     * @Method add
     * @Param
     * @param tableTransferListRequestDto
     * @Return void
     * @Description 表迁移清单-新增
     * @Author Wang zhihao
     * @Date 2023/8/9 11:36
     * @Version V1.0
     */
    @Override
    public void add(TableTransferListRequestDto tableTransferListRequestDto) {
        int count = tableTransferListRepository.add(converter.toTableTransferListPo(tableTransferListRequestDto));
        if (count != 1) {
            throw new RuntimeException("新增表迁移清单失败");
        }
    }

    /**
     * @Method modifyById
     * @Param
     * @param tableTransferListRequestDto
     * @Return void
     * @Description 表迁移清单-根据主键修改
     * @Author Wang zhihao
     * @Date 2023/8/9 11:36
     * @Version V1.0
     */
    @Override
    public void modifyById(TableTransferListRequestDto tableTransferListRequestDto) {
        TableTransferListPo tableTransferListPo = converter.toTableTransferListPo(tableTransferListRequestDto);
        tableTransferListPo.setTargetDbName(tableTransferListPo.getTargetDbName().toUpperCase());
        tableTransferListPo.setTargetTableName(tableTransferListPo.getTargetTableName().toUpperCase());
        if (tableTransferListRequestDto.getPartitionFieldNameList() != null) {
            tableTransferListPo.setPartitionFieldName(StringUtils.join(tableTransferListRequestDto.getPartitionFieldNameList().toArray(), ","));
        }
        int count = tableTransferListRepository.modifyById(tableTransferListPo);
        if (count != 1) {
            throw new RuntimeException("修改表迁移清单失败");
        }
    }

    /**
     * @Method removeById
     * @Param
     * @param pkId
     * @Return void
     * @Description 表迁移清单-根据pkId删除
     * @Author Wang zhihao
     * @Date 2023/8/9 11:37
     * @Version V1.0
     */
    @Override
    public void removeById(String pkId) {
        decreaseTransferTotal(pkId);
        int count = tableTransferListRepository.removeById(pkId);
        tableTransferFieldMapService.removeByListPkId(pkId);
        if (count != 1) {
            throw new RuntimeException("删除表迁移清单失败");
        }
    }

    /**
     * @Method decreaseTransferTotal
     * @Param
     * @param pkId
     * @Return void
     * @Description 减少表迁移清单总数
     * @Author Wang zhihao
     * @Date 2023/8/25 10:10
     * @Version V1.0
     */
    private void decreaseTransferTotal(String pkId) {
        String taskPkId = queryById(pkId).getTaskPkId();
        tableTransferTaskService.modifyTransferTotal(taskPkId, "decrease");
    }

    /**
     * @Method batchRemoveById
     * @Param
     * @param pkIds
     * @Return void
     * @Description 表迁移清单-批量删除
     * @Author Wang zhihao
     * @Date 2023/8/9 10:44
     * @Version V1.0
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public void batchRemoveById(List<String> pkIds) {
        for (String pkId : pkIds) {
            removeById(pkId);
        }
    }


    /**
     * @Method removeByTaskPkId
     * @Param
     * @param taskPkId
     * @Return void
     * @Description 表迁移清单-根据任务主键删除
     * @Author Wang zhihao
     * @Date 2023/8/9 19:32
     * @Version V1.0
     */
    @Override
    public void removeByTaskPkId(String taskPkId) {
        List<TableTransferListResponseDto> tableTransferLists = queryList(new TableTransferListRequestDto().setTaskPkId(taskPkId));
        for (TableTransferListResponseDto tableTransferList : tableTransferLists) {
            removeById(tableTransferList.getPkId());
        }
    }

    /**
     * @Method buildCreateTableSql
     * @Param
     * @param pkId
     * @Return com.hex.ds.hdtp.core.app.data.table.dto.response.TableTransferListResponseDto
     * @Description 表迁移清单-建立建表语句
     * @Author Wang zhihao
     * @Date 2023/8/18 17:37
     * @Version V1.1
     */
    @Override
    public TableTransferListResponseDto buildCreateTableSql(String pkId) {
        TableTransferListResponseDto tableTransferListResponseDto = new TableTransferListResponseDto();
        // ① 通过清单主键获取表迁移信息
        TableTransferListPo tableTransferList = tableTransferListRepository.queryById(pkId);
        // ② 通过数据源连接编号获取数据源信息
        SourceInfoPo sourceConnInfoPo = sourceInfoService.queryByPkId(tableTransferList.getSourceConnPkId());
        SourceInfoPo targetConnInfoPo = sourceInfoService.queryByPkId(tableTransferList.getTargetConnPkId());
        // ③ 获取源表的建表SQL
        String sourceCreateSql = getSoureCreateSql(sourceConnInfoPo, tableTransferList);
        // ④ 生成目标表的建表SQL
        String targetCreateSql = getTargetCreateSQL(targetConnInfoPo, tableTransferList, tableTransferList.getTransferMethod());
        // ⑤ 返回建表SQL
        tableTransferListResponseDto.setSourceCreateSql(sourceCreateSql);
        tableTransferListResponseDto.setTargetCreateSql(targetCreateSql);
        return tableTransferListResponseDto;
    }

    /**
     * @Method addWithBackId
     * @Param
     * @param tableTransferListRequestDto
     * @Return java.lang.String
     * @Description 新增并返回主键
     * @Author Wang zhihao
     * @Date 2023/8/14 10:02
     * @Version V1.0
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public String addWithBackId(TableTransferListRequestDto tableTransferListRequestDto) {
        TableTransferListPo tableTransferListPo = converter.toTableTransferListPo(tableTransferListRequestDto);
        String sourceType = sourceInfoService.queryByPkId(tableTransferListRequestDto.getSourceConnPkId()).getSourceType();
        String targetType = sourceInfoService.queryByPkId(tableTransferListRequestDto.getTargetConnPkId()).getSourceType();
        List<TableConvertResponseDto> tableConvertResponseDtos = getTableConvertInfo(sourceType, targetType);
        Optional<TableConvertResponseDto> convertDb = tableConvertResponseDtos.stream()
                .filter(t -> "databaseName".equals(t.getConvertType()) && tableTransferListPo.getSourceDbName().toUpperCase().equals(t.getOriginalValue().toUpperCase()))
                .findFirst();
        Optional<TableConvertResponseDto> convertTable = tableConvertResponseDtos.stream()
                .filter(t -> "tableName".equals(t.getConvertType()) && tableTransferListPo.getSourceTableName().toUpperCase().equals(t.getOriginalValue().toUpperCase()))
                .findFirst();
        String targetDbName = convertDb.isPresent() ? convertDb.get().getTargetValue() : tableTransferListPo.getSourceDbName();
        String targetTableName = convertTable.isPresent() ? convertTable.get().getTargetValue() : tableTransferListPo.getSourceTableName();
        tableTransferListPo.setTargetDbName(targetDbName.toUpperCase());
        tableTransferListPo.setTargetTableName(targetTableName.toUpperCase());
        String pkId = tableTransferListRepository.addWithBackId(tableTransferListPo);
        tableTransferTaskService.modifyTransferTotal(tableTransferListRequestDto.getTaskPkId(), "increase");
        return pkId;
    }

    /**
     * @Method getSoureCreateSql
     * @Param
     * @param connInfoPo
     * @param tableTransferList
     * @Return void
     * @Description 通过JDBC获取源表的建表SQL
     * @Author Wang zhihao
     * @Date 2023/8/11 16:45
     * @Version V1.0
     */
    private String getSoureCreateSql(SourceInfoPo connInfoPo, TableTransferListPo tableTransferList) {
        // ① 通过JDBC获取源表SQL
        String sourceCreateSql = dataInfoSelectHandlerContext.getDataInfoService(connInfoPo.getSourceType())
                .selectCreateTableSql(connInfoPo.getSourceCode(), tableTransferList.getSourceDbName(), tableTransferList.getSourceTableName());
        // ② 保存建表语句
        tableTransferList.setSourceCreateSql(sourceCreateSql);
        tableTransferListRepository.modifyById(tableTransferList);
        return sourceCreateSql;
    }

    /**
     * @Method getTargetCreateSQL
     * @Param
     * @param connInfoPo
     * @param tableTransferList
     * @Return void
     * @Description 建立生成目标的建表SQL
     * @Author Wang zhihao
     * @Date 2023/8/11 16:46
     * @Version V1.0
     */
    private String getTargetCreateSQL(SourceInfoPo connInfoPo, TableTransferListPo tableTransferList, String transferMethod) {
        // ① 获取字段映射
        TableTransferFieldMapRequestDto tableTransferFieldMapRequestDto = new TableTransferFieldMapRequestDto();
        tableTransferFieldMapRequestDto.setListPkId(tableTransferList.getPkId());
        List<TableTransferFieldMapResponseDto> tableTransferFieldMapList = tableTransferFieldMapService.queryList(tableTransferFieldMapRequestDto);
        // ② 生成表结构信息
        List<TableFieldsEntity> tableFieldsEntities = tableTransferFieldMapConverter.toTableFieldsEntityList(tableTransferFieldMapList);
        tableFieldsEntities.forEach(item -> item.setDataBaseName(tableTransferList.getTargetDbName()));
        tableFieldsEntities.forEach(item -> item.setTableName(tableTransferList.getTargetTableName()));
        String targetCreateSql = dataInfoSelectHandlerContext.getDataInfoService(connInfoPo.getSourceType())
                .buildCreateTableSql(tableFieldsEntities, Arrays.asList(tableTransferList.getPartitionFieldName().toUpperCase().split(",")), transferMethod);
        // ③ 保存建表语句
        tableTransferList.setTargetCreateSql(targetCreateSql);
        tableTransferListRepository.modifyById(tableTransferList);
        return targetCreateSql;
    }

    /**
     * @Method getFieldMap
     * @Param
     * @param sourceConnId
     * @param targetConnId
     * @param pkId
     * @Return void
     * @Description 表迁移清单-获取字段映射
     * @Author Wang zhihao
     * @Date 2023/8/14 14:47
     * @Version V1.0
     */
    @Override
    public void getFieldMap(String sourceConnId, String targetConnId, String pkId) {
        // ① 通过清单主键获取表迁移信息
        TableTransferListPo tableTransferList = tableTransferListRepository.queryById(pkId);
        // ② 获取源的字段信息
        List<TableFieldsEntity> tableFieldsEntities = sourceInfoService.tablesStructure(sourceConnId, tableTransferList.getSourceDbName(), tableTransferList.getSourceTableName());
        List<TableTransferFieldMapRequestDto> tableTransferFieldMapRequestDtos = tableTransferFieldMapConverter.toFieldMapRequestDtoList(tableFieldsEntities);
        tableTransferFieldMapRequestDtos.forEach(item -> item.setListPkId(pkId));
        // ③ 获取表映射信息
        String sourceType = sourceInfoService.queryByPkId(sourceConnId).getSourceType();
        String targetType = sourceInfoService.queryByPkId(targetConnId).getSourceType();
        List<TableConvertResponseDto> tableConvertResponseDtos = getTableConvertInfo(sourceType, targetType);
        // ④ 数据映射重组
        reGroupData(tableFieldsEntities, tableConvertResponseDtos, tableTransferFieldMapRequestDtos);
    }

    /**
     * @Method queryLatestSuccess
     * @Param
     * @param tableTransferListRequestDto
     * @Return com.hex.ds.hdtp.core.app.common.page.PageData
     * @Description 表迁移清单-分页查询每个建表最新成功的
     * @Author Wang zhihao
     * @Date 2023/8/28 11:04
     * @Version V1.0
     */
    @Override
    public PageData queryLatestSuccess(TableTransferListRequestDto tableTransferListRequestDto, PageDto pageDto) {
        final Page<TableTransferListPo> pageResult = tableTransferListRepository.queryLatestSuccess(
                converter.toTableTransferListPo(tableTransferListRequestDto), pageAndPageDtoConverter.toPage(pageDto));
        List<TableTransferListResponseDto> responseDtos = converter.toTableTransferListResponseDtoList(pageResult.getData());
        responseDtos.forEach(responseDto -> {
            responseDto.setSourceConnName(sourceInfoService.queryByPkId(responseDto.getSourceConnPkId()).getSourceName());
            responseDto.setTargetConnName(sourceInfoService.queryByPkId(responseDto.getTargetConnPkId()).getSourceName());
        });
        return new PageData(responseDtos, pageResult.getTotal());
    }

    @Override
    public ByteArrayResource exportTableTransferList(List<String> pkIds) {
        String backUpPath = createSavePath();
        String templateFilePath = "static/TableTransferTemplate.xlsx";
        List<TableTransferListExcelDto> tableTransferListExcelDtos = pkIds.stream()
                .map(this::queryById)
                .map(tableTransferListResponseDto -> {
                    TableTransferListExcelDto tableTransferListExcelDto = new TableTransferListExcelDto();
                    BeanUtils.copyProperties(tableTransferListResponseDto, tableTransferListExcelDto);
                    return tableTransferListExcelDto;
                })
                .collect(Collectors.toList());
        String saveFilePath = backUpPath + File.separator + "表迁移清单模板.xlsx";
        ExcelDownUtil.generatedExcel(tableTransferListExcelDtos, templateFilePath, saveFilePath);
        byte[] bytes;
        try {
            bytes = Files.readAllBytes(new File(saveFilePath).toPath());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return new ByteArrayResource(bytes);
    }

    @Override
    public Map<String, Object> importTableTransferList(MultipartFile file,TableTransferListExcelDto tableTransferListExcelDto) {
        Map<String, Object> resultMap = new HashMap<>();
        try {
            String backUpFilePath = backupUploadFile(file);
            List<TableTransferListExcelDto> tableTransferListExcelDtos = getExcelData(backUpFilePath);
            tableTransferListExcelDtos.forEach(dto -> BeanUtils.copyProperties(tableTransferListExcelDto, dto, getIgnoreString(tableTransferListExcelDto).toArray(new String[0])));
            String taskId = saveForExcel(tableTransferListExcelDtos);
            resultMap.put("status", true);
            resultMap.put("taskId",taskId);
        } catch (Exception e) {
            resultMap.put("status", false);
            resultMap.put("message", e.getMessage());
        }
        return resultMap;
    }

    /**
     * @Method reGroupData
     * @Param
     * @param tableFieldsEntities
     * @param tableConvertResponseDtos
     * @param tableTransferFieldMapRequestDtos
     * @Return void
     * @Description 数据映射重组
     * @Author Wang zhihao
     * @Date 2023/8/15 11:56
     * @Version V1.0
     */
    public void reGroupData(List<TableFieldsEntity> tableFieldsEntities, List<TableConvertResponseDto> tableConvertResponseDtos, List<TableTransferFieldMapRequestDto> tableTransferFieldMapRequestDtos) {
        try {
            // ① 字段名的映射
            List<String> newFieldsName = fieldNameMapping(tableFieldsEntities, tableConvertResponseDtos);
            // ② 字段类型的映射
            List<String> newFieldsType = fieldTypeMapping(tableFieldsEntities, tableConvertResponseDtos);
            // 字段长度的映射
            List<String> newFieldsLength = fieldLengthMapping(tableFieldsEntities, tableConvertResponseDtos);
            // ③ 整合字段信息
            List<TableTransferFieldMapRequestDto> tableTransferFieldMapRequest = integrateInfo(tableTransferFieldMapRequestDtos, newFieldsName, newFieldsType, newFieldsLength, tableFieldsEntities);
            // ④ 入库
            tableTransferFieldMapService.batchAdd(tableTransferFieldMapRequest);
        } catch (Exception e) {
            log.error("表迁移清单-获取字段映射异常: {}", e.getMessage(), e);
        }

    }

    /**
     * @Method getTableConvertInfo
     * @Param
     * @param originalServer
     * @param targetServer
     * @Return java.util.List<com.hex.ds.hdtp.core.app.data.configuration.dto.response.TableConvertResponseDto>
     * @Description 获取表映射信息
     * @Author Wang zhihao
     * @Date 2023/8/14 20:36
     * @Version V1.0
     */
    public List<TableConvertResponseDto> getTableConvertInfo(String originalServer, String targetServer) {
        return tableConvertService.queryList(new TableConvertRequestDto().setOriginalServer(originalServer).setTargetServer(targetServer));
    }

    /**
     * @Method fieldNameMapping
     * @Param
     * @param tableFieldsEntities
     * @param tableConvertResponseDtos
     * @Return java.util.List<java.lang.String>
     * @Description 根据字段名称的映射获取新的字段信息
     * @Author Wang zhihao
     * @Date 2023/8/14 20:35
     * @Version V1.0
     */
    public List<String> fieldNameMapping(List<TableFieldsEntity> tableFieldsEntities, List<TableConvertResponseDto> tableConvertResponseDtos) {
        // ① 过滤出字段名称的映射并转为Map<源类型, 目标类型>的方式
        Map<String, String> fieldsMap = tableConvertResponseDtos.stream()
                .filter(map -> map.getConvertType().equals("fieldName"))
                .collect(Collectors.toMap(TableConvertResponseDto::getOriginalValue, TableConvertResponseDto::getTargetValue, (key1, key2) -> key2));
        // ② 从字段信息中提取出字段名称
        List<String> fieldsName = tableFieldsEntities.stream().map(TableFieldsEntity::getColumnName).collect(Collectors.toList());
        List<String> newFieldsName = new ArrayList<>();
        for (String fieldName : fieldsName) {
            newFieldsName.add((fieldsMap.get(fieldName) == null) ? fieldName : fieldsMap.get(fieldName) );
        }
        return newFieldsName;
    }

    /**
     * @Method fieldTypeMapping
     * @Param
     * @param tableFieldsEntities
     * @param tableConvertResponseDtos
     * @Return java.util.List<java.lang.String>
     * @Description 根据字段类型的映射获取新的字段类型信息
     * @Author Wang zhihao
     * @Date 2023/8/15 9:44
     * @Version V1.0
     */
    public List<String> fieldTypeMapping(List<TableFieldsEntity> tableFieldsEntities, List<TableConvertResponseDto> tableConvertResponseDtos) {
        // ① 过滤出字段类型的映射并转为Map<源类型, 目标类型>的方式
        Map<String, String> fieldsMap = tableConvertResponseDtos.stream()
                .filter(map -> map.getConvertType().equals("fieldType"))
                .collect(Collectors.toMap(TableConvertResponseDto::getOriginalValue, TableConvertResponseDto::getTargetValue, (key1, key2) -> key2));
        // ② 从字段信息中提取出字段类型信息
        List<String> fieldsType = tableFieldsEntities.stream().map(TableFieldsEntity::getColumnType).collect(Collectors.toList());
        List<String> newFieldsType = new ArrayList<>();
        for (String fieldType : fieldsType) {
            newFieldsType.add((fieldsMap.get(fieldType) == null) ? fieldType : fieldsMap.get(fieldType) );
        }
        return newFieldsType;
    }

    /**
     * @Method fieldLengthMapping
     * @Param
     * @param tableFieldsEntities
     * @param tableConvertResponseDtos
     * @Return java.util.List<java.lang.String>
     * @Description 根据字段长度的映射获取新的字段类型信息
     * @Author Wang zhihao
     * @Date 2023/8/15 10:13
     * @Version V1.0
     */
    public List<String> fieldLengthMapping(List<TableFieldsEntity> tableFieldsEntities, List<TableConvertResponseDto> tableConvertResponseDtos) {
        // ① 过滤出字段长度的映射并转为<字段类型=>字段类型, 系数>的方式
        Map<String, Float> fieldsMap = tableConvertResponseDtos.stream()
                .filter(map -> map.getConvertType().equals("fieldType"))
                .collect(Collectors.toMap(TableConvertResponseDto::getOriginalValue, TableConvertResponseDto::getCoefficient, (key1, key2) -> key2 ));
        List<String> fieldsType = tableFieldsEntities.stream().map(TableFieldsEntity::getColumnType).collect(Collectors.toList());
        List<String> fieldsLength = tableFieldsEntities.stream().map(TableFieldsEntity::getColumnLength).collect(Collectors.toList());
        List<String> newFieldsLength = new ArrayList<>();
        for (int i = 0 ; i < tableFieldsEntities.size() ; i++) {
            //处理字符串类型 length * 系数
            if (fieldsMap.get(fieldsType.get(i)) != null && fieldsMap.get(fieldsType.get(i)) > 0) {
                String lengthString = fieldsLength.get(i).replace(",", ""); // 去除逗号
                newFieldsLength.add(String.valueOf(Math.round( Integer.parseInt(lengthString) * fieldsMap.get(fieldsType.get(i)) )));
            } else if (fieldsMap.get(fieldsType.get(i)) != null && fieldsMap.get(fieldsType.get(i)) == -1) {
                newFieldsLength.add(fieldsLength.get(i));
            } else if(fieldsMap.get(fieldsType.get(i)) != null && fieldsMap.get(fieldsType.get(i)) == 0) {
                newFieldsLength.add("0");
            } else {
                newFieldsLength.add(fieldsLength.get(i));
            }
        }
        return newFieldsLength;
    }

    /**
     * @Method integrateInfo
     * @Param
     * @param tableTransferFieldMapRequestDtos
     * @param fieldName
     * @param fieldType
     * @Return java.util.List<com.hex.ds.hdtp.core.app.data.table.dto.request.TableTransferFieldMapRequestDto>
     * @Description 整合数据
     * @Author Wang zhihao
     * @Date 2023/8/15 11:33
     * @Version V1.0
     */
    public List<TableTransferFieldMapRequestDto> integrateInfo(List<TableTransferFieldMapRequestDto> tableTransferFieldMapRequestDtos
            , List<String> fieldName, List<String> fieldType, List<String> fieldLength, List<TableFieldsEntity> tableFieldsEntities) throws Exception {
        int flag = 0;
        for (TableTransferFieldMapRequestDto tableTransferFieldMapRequestDto : tableTransferFieldMapRequestDtos) {
            tableTransferFieldMapRequestDto.setTargetFieldName(fieldName.get(flag));
            tableTransferFieldMapRequestDto.setTargetFieldType(fieldType.get(flag));
            tableTransferFieldMapRequestDto.setTargetFieldLength(fieldLength.get(flag));
            tableTransferFieldMapRequestDto.setFieldOrder(Integer.valueOf(tableFieldsEntities.get(flag).getColumnId()));
            flag ++;
        }
        return tableTransferFieldMapRequestDtos;
    }

    private String createSavePath() {
        String savePath = filePath + File.separator + "static/downExcel/TableTransferTemplate" + File.separator + DateUtil.today() + File.separator + DateUtil.current(false);
        FileUtil.mkdir(savePath);
        return FilenameUtils.separatorsToSystem(savePath);
    }

    private String backupUploadFile(MultipartFile multipartFile) {
        String backUpPath = FilenameUtils.separatorsToSystem(filePath + File.separator + "static/uploadFile/TableTransferTemplate" + File.separator + DateUtil.today() + File.separator + DateUtil.current(false));
        FileUtil.mkdir(backUpPath);
        String updateFilePath = FilenameUtils.separatorsToSystem(backUpPath + File.separator);
        File file = upload(multipartFile, updateFilePath);
        log.debug("上传到服务器的EXCEL文件路径: {}", file.getPath());
        return file.getPath();
    }

    private List<TableTransferListExcelDto> getExcelData(String backUpFilePath) {
        ExcelImportResult<TableTransferListExcelDto> convertAfterList = readExcel(backUpFilePath);
        return convertAfterList.getList();
    }

    private ExcelImportResult<TableTransferListExcelDto> readExcel(String backUpFilePath) {
        ImportParams importParams = new ImportParams();
        importParams.setStartRows(1);
        return ExcelImportUtil.importExcelMore(new File(backUpFilePath), TableTransferListExcelDto.class, importParams);
    }
    @Transactional
    public String saveForExcel(List<TableTransferListExcelDto> tableTransferListExcelDtos) {
        String[] ignoreParam= {"pkId"};
        TableTransferTaskRequestDto tableTransferTaskRequestDto = new TableTransferTaskRequestDto();
        BeanUtils.copyProperties(tableTransferListExcelDtos.get(0), tableTransferTaskRequestDto);
        String taskPkId = (tableTransferTaskRequestDto.getPkId() == null || tableTransferTaskRequestDto.getPkId().isEmpty())
                ? tableTransferTaskService.addWithBackId(tableTransferTaskRequestDto)
                : tableTransferTaskRequestDto.getPkId();
        tableTransferListExcelDtos
                .forEach(tableTransferListExcelDto -> {
                    TableTransferListRequestDto tableTransferListRequestDto = new TableTransferListRequestDto();
                    TableTransferListPo tableTransferList = new TableTransferListPo();
                    if (tableTransferListExcelDto.getPartitionFieldName() == null || tableTransferListExcelDto.getPartitionFieldName().isEmpty()) {tableTransferListExcelDto.setPartitionFieldName("DT");}
                    BeanUtils.copyProperties(tableTransferListExcelDto, tableTransferListRequestDto,ignoreParam);
                    BeanUtils.copyProperties(tableTransferListExcelDto, tableTransferList,ignoreParam);
                    tableTransferListRequestDto.setTaskPkId(taskPkId);
                    tableTransferList.setTaskPkId(taskPkId);
                    String pkId = this.addWithBackId(tableTransferListRequestDto);
                    this.getFieldMap(tableTransferListExcelDto.getSourceConnPkId(), tableTransferListExcelDto.getTargetConnPkId(), pkId);
                    this.buildCreateTableSql(pkId);
                });
        return taskPkId;
    }

    private List<String> getIgnoreString(TableTransferListExcelDto tableTransferListExcelDto){
        ArrayList<String> ignoreString = new ArrayList<>();
        ignoreString.add("sourceDbName");
        ignoreString.add("sourceTableName");
        if(tableTransferListExcelDto.getTargetDbName() == null || tableTransferListExcelDto.getTargetDbName().isEmpty())ignoreString.add("targetDbName");
        if(tableTransferListExcelDto.getTargetTableName() == null || tableTransferListExcelDto.getTargetTableName().isEmpty())ignoreString.add("targetTableName");
        if(tableTransferListExcelDto.getTargetCreateSql() == null || tableTransferListExcelDto.getTargetCreateSql().isEmpty())ignoreString.add("targetCreateSql");
        if(tableTransferListExcelDto.getPartitionFieldName() == null || tableTransferListExcelDto.getPartitionFieldName().isEmpty())ignoreString.add("partitionFieldName");
        if(tableTransferListExcelDto.getTableTransferFieldMapList() == null ||tableTransferListExcelDto.getTableTransferFieldMapList().isEmpty())ignoreString.add("tableTransferFieldMapList");
        return ignoreString;
    }

}
